package JDBC;

import java.sql.*;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;

/**
 * 测试时间处理(java.sql.Date,Time,Timestamp)  实现查询日期之间的数据
 */
public class demo7 {
    /**
     * 将字符串代表的日期转为long类型数字(格式 yyyy-MM-dd hh:mm:ss)
     * @param dateStr
     * @return
     */
    public static long str2Date(String dateStr){
        DateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        try {
            return format.parse(dateStr).getTime();//字符串转为时间对象在获取时间戳
        } catch (ParseException e) {
            e.printStackTrace();
            return 0;
        }
    }
    public static void main(String[] args) {
        Connection conn= null;
        PreparedStatement ps1 = null;
        PreparedStatement ps2 = null;
        ResultSet rs = null;
        try {
            //加载类驱动
            Class.forName("com.mysql.jdbc.Driver");
            conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/zz?characterEncoding=UTF-8","root","123456");

            ps1 = conn.prepareStatement("INSERT INTO user (userName,age,regTime,loginTime) VALUES (?,?,?,?)");
            ps1.setObject(1,"赵六"+Math.floor(Math.random()*100));
            ps1.setObject(2,100);
            Date data = new Date(System.currentTimeMillis());//插入当前时间
            ps1.setObject(3,data);
            Timestamp stamp = new Timestamp(System.currentTimeMillis());//如果需要指定日期时间，可以使用Calender,DateFormat
            ps1.setObject(4,stamp);
            ps1.execute();

            //查询时间区间
            ps2 = conn.prepareStatement("SELECT * FROM user WHERE loginTime BETWEEN ? AND ?");
            Timestamp start = new Timestamp(str2Date("2019-02-27 11:41:56"));
            Timestamp end = new Timestamp(str2Date("2019-02-27 11:49:51"));
            ps2.setObject(1,start);
            ps2.setObject(2,end);
            rs = ps2.executeQuery();

            while (rs.next()){
                System.out.println(rs.getInt("id")+"---"+rs.getString("userName")+"---"+rs.getTimestamp("loginTime"));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(rs!=null){
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if(ps2!=null){
                    ps2.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if(ps1!=null){
                    ps1.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if(conn!=null){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
